# -*- coding:utf-8 -*-
# @Time     :2024/3/14 21:35
# @Author   :CHNJX
# @File     :performance_monitor.py
# @Desc     :日志监控
import os
import telnetlib
import time
from datetime import datetime
from openpyxl import load_workbook, Workbook
from openpyxl.utils.dataframe import dataframe_to_rows
from openpyxl.chart import LineChart, Reference
from openpyxl.styles import Font
import pandas as pd


class PerformanceMonitor:
    def __init__(self, host, port, user, password, command, filename, sheet_name):
        self.host = host
        self.port = port
        self.user = user
        self.password = password
        self.command = command
        self.filename = filename
        self.sheet_name = sheet_name

    def get_system_usage(self):
        """使用Telnet从设备获取系统使用情况"""
        try:
            with telnetlib.Telnet(self.host, self.port, timeout=10) as tn:
                tn.read_until(b"login: ")
                tn.write(self.user.encode('ascii') + b"\n")
                tn.read_until(b"Password: ")
                tn.write(self.password.encode('ascii') + b"\n")
                tn.write(self.command.encode('ascii') + b"\n")
                time.sleep(3)  # 等待命令执行

                output = tn.read_very_eager().decode('ascii')
                return self.parse_usage_output(output)
        except Exception as e:
            print(f"Error: {e}")
            return None, None

    def parse_usage_output(self, output):
        """解析系统使用情况的输出"""
        cpu_usage = memory_usage = None
        for line in output.splitlines():
            if "CPU_USAGE=" in line:
                cpu_usage = float(line.split("=")[1])
            elif "MEM_USAGE=" in line:
                memory_usage = float(line.split("=")[1])
        return cpu_usage, memory_usage

    def collect_and_save_data(self, duration, interval_time):
        """
        收集一定时间内的性能数据，并保存到Excel
        :param duration: 持续时间
        :param interval_time: 间隔时间
        :return:
        """
        data = []
        start_time = datetime.now()
        while (datetime.now() - start_time).seconds < duration:
            cpu_usage, memory_usage = self.get_system_usage()
            if cpu_usage is not None and memory_usage is not None:
                data.append(
                    {'Time': datetime.now().strftime('%H:%M:%S'), 'CPU_Usage': cpu_usage, 'Memory_Usage': memory_usage})
            time.sleep(interval_time)  # 每隔一段时间收集一次数据

        # 将数据转换为DataFrame，然后保存到Excel
        self.save_data_to_excel(pd.DataFrame(data))

    def save_data_to_excel(self, df):
        """将数据保存到Excel文件"""
        workbook = load_workbook(self.filename) if os.path.exists(self.filename) else Workbook()
        if self.sheet_name in workbook.sheetnames:
            del workbook[self.sheet_name]
        sheet = workbook.create_sheet(self.sheet_name)

        for r in dataframe_to_rows(df, index=False, header=True):
            sheet.append(r)

        workbook.save(self.filename)
        self.add_charts_and_formatting_to_sheet(self.filename, self.sheet_name)

    def add_charts_and_formatting_to_sheet(self, filename, sheet_name, cpu_threshold=90, mem_threshold=95):
        book = load_workbook(filename)
        sheet = book[sheet_name]

        # 创建CPU使用率图表
        cpu_chart = LineChart()
        cpu_chart.title = "CPU Usage"
        cpu_chart.style = 13
        cpu_chart.y_axis.title = 'CPU Usage (%)'
        cpu_chart.x_axis.title = 'Time'
        cpu_chart.width = 30  # 图表宽度
        cpu_chart.height = 10  # 图表高度

        # 创建内存使用率图表
        mem_chart = LineChart()
        mem_chart.title = "Memory Usage"
        mem_chart.style = 13
        mem_chart.y_axis.title = 'Memory Usage (%)'
        mem_chart.x_axis.title = 'Time'

        mem_chart.width = 30  # 图表宽度
        mem_chart.height = 10  # 图表高度

        # 添加CPU使用率数据到图表
        cpu_data = Reference(sheet, min_col=2, min_row=1, max_col=2, max_row=sheet.max_row)
        cpu_chart.add_data(cpu_data, titles_from_data=True)

        # 添加时间作为图表的X轴
        times = Reference(sheet, min_col=1, min_row=2, max_row=sheet.max_row)
        cpu_chart.set_categories(times)

        # 同理添加内存使用率数据到图表
        mem_data = Reference(sheet, min_col=3, min_row=1, max_col=3, max_row=sheet.max_row)
        mem_chart.add_data(mem_data, titles_from_data=True)
        mem_chart.set_categories(times)

        # 将图表添加到工作表
        sheet.add_chart(cpu_chart, "E2")
        sheet.add_chart(mem_chart, "E20")

        book.save(filename)

        # 请根据前面的示例添加创建和添加图表的代码

        # 字体样式：红色
        red_font = Font(color="FF0000")

        # 检查并格式化超过阈值的CPU使用率单元格
        for row in range(2, sheet.max_row + 1):  # 从第2行开始到最大行数
            cpu_cell = sheet.cell(row=row, column=2)  # CPU使用率在第二列
            if cpu_cell.value is not None and cpu_cell.value > cpu_threshold:
                cpu_cell.font = red_font

        # 检查并格式化超过阈值的内存使用率单元格
        for row in range(2, sheet.max_row + 1):
            mem_cell = sheet.cell(row=row, column=3)  # 内存使用率在第三列
            if mem_cell.value is not None and mem_cell.value > mem_threshold:
                mem_cell.font = red_font

        book.save(filename)
